package org.redxentertainment.videomanager;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class VideoDatabase extends SQLiteOpenHelper {
	
	//-------------------------------------------------------------------------------------
	public VideoDatabase(Context context) {
		super(context, "videoDatabase", null, 1);
		
	}

	//-------------------------------------------------------------------------------------
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS videos(id INT, category VARCHAR(255), watched INT)");
		//db.execSQL("DROP TABLE queue");
		db.execSQL("CREATE TABLE IF NOT EXISTS queue(row INTEGER PRIMARY KEY AUTOINCREMENT, id INT)");
	}

	//-------------------------------------------------------------------------------------
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE videos");
		db.execSQL("DROP TABLE queue");
		db.execSQL("CREATE TABLE IF NOT EXISTS videos(id INT, category VARCHAR(255), watched INT)");
		db.execSQL("CREATE TABLE IF NOT EXISTS queue(row INTEGER PRIMARY KEY AUTOINCREMENT, id INT)");
	}
	
	//-------------------------------------------------------------------------------------
	@Override
 	public void onOpen(SQLiteDatabase  db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS queue(row INTEGER PRIMARY KEY AUTOINCREMENT, id INT)");
	}
	
	//-------------------------------------------------------------------------------------
	public boolean videoExists(Video video) {
		String tmp;
		
		if(video == null) {
			return true;
		}
		
		Cursor cursor = getReadableDatabase().rawQuery("SELECT category FROM videos WHERE(id=" + video.id + ")", null);
		
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			tmp = cursor.getString(0);
			
			if(tmp != null) {
				if(tmp.compareTo(video.album) == 0) {
					cursor.close();
					return true;
				}
			}
		}
		cursor.close();
		return false;
	}
	
	
	public ArrayList<Category> getCategories() {
		Category			category;
		ArrayList<Category> categories 			= new ArrayList<Category>();
		ArrayList<Category> returnCategories 	= new ArrayList<Category>();
		SQLiteDatabase 		db 					= getReadableDatabase();
		
		Cursor cursor = db.rawQuery("SELECT DISTINCT category FROM videos", null);
		cursor.moveToFirst();
		while(! cursor.isAfterLast() ) {
			category 		= new Category();
			category.name 	= cursor.getString(0);
			categories.add(category);
			cursor.moveToNext();
		}
		cursor.close();
		
		
		cursor = db.rawQuery("SELECT COUNT(id) FROM videos", null);		
		category = new Category();
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			category.videos = cursor.getInt(0);
		}
		cursor.close();
		category.name = "All Videos";
		returnCategories.add(category);
		
		
		for(Category cat : categories) {
			cursor = db.rawQuery("SELECT COUNT(id) FROM videos WHERE(category=\"" + cat.name + "\")", null);
			
			cursor.moveToFirst();
			if(! cursor.isAfterLast() ) {
				cat.videos = cursor.getInt(0);
			}
			returnCategories.add(cat);
			cursor.close();
		}
		if(! cursor.isClosed() ) {
			cursor.close();
		}

		return returnCategories;
	}
	
	public boolean videoWasWatched(Video video) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT watched FROM videos WHERE(id=" + video.id + ")", null);
		
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			if( cursor.getInt(0) == 1) {
				cursor.close();
				return true;
			}
		}
		cursor.close();
		return false;
	}
	
	public void setVideoWatched(long id) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("UPDATE videos SET watched=1 WHERE(id=" + id + ")");
		db.close();
	}
	
	//-------------------------------------------------------------------------------------
	public boolean addVideo(Video video) {		
		if( videoExists(video) ) {
			return true;
		} else {
			SQLiteDatabase db = getWritableDatabase();
			db.execSQL("INSERT INTO videos VALUES(" + video.id + ", \"" + video.album + "\", 0)");
			db.close();
			return false;
		}
	}
	
	
	public void clearDatabase() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DELETE FROM videos");
		db.close();
	}
	
	public void enqueue(long id) {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("INSERT INTO queue VALUES(null, " + id + ")");
		db.close();
	}
	
	public void enqueueCategory(String category) {
		Cursor 			cursor;
		SQLiteDatabase 	db = getWritableDatabase();
		
		cursor = db.rawQuery("SELECT id FROM videos WHERE(category=\"" + category + "\")", null);
		
		cursor.moveToFirst();
		while(! cursor.isAfterLast() ) {
			db.execSQL("INSERT INTO queue VALUES(null, " + cursor.getInt(0) + ")");
			
			cursor.moveToNext();
		}
		cursor.close();
	}
		
	public long dequeue() {
		long			id;
		SQLiteDatabase 	db 		= getWritableDatabase();
		Cursor 			cursor 	= db.rawQuery("SELECT row, id FROM queue ORDER BY row DESC LIMIT 1", null);
		
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			id = cursor.getLong(1);
			db.execSQL("DELETE from queue WHERE(row=" + cursor.getInt(0) + ")");
			cursor.close();
			return id;
		} else {
			cursor.close();
			return 0;
		}
	}
	
	public long dequeueRandom() {
		long			id;
		SQLiteDatabase 	db 		= getWritableDatabase();
		Cursor 			cursor 	= db.rawQuery("SELECT row, id FROM queue ORDER BY RANDOM() LIMIT 1", null);
		
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			id = cursor.getLong(1);
			db.execSQL("DELETE from queue WHERE(row=" + cursor.getInt(0) + ")");
			cursor.close();
			return id;
		} else {
			cursor.close();
			return 0;
		}
	}
	
	public boolean videoisQueued(int id) {
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = db.rawQuery("SELECT row FROM queue WHERE(id=" + id + ")", null);
		
		cursor.moveToFirst();
		if(! cursor.isAfterLast() ) {
			cursor.close();
			return true;
		}
		cursor.close();
		return false;
	}
	
	public void clearQueue() {
		SQLiteDatabase db = getWritableDatabase();
		db.execSQL("DELETE FROM queue");
	}
}
